with sign_tmp as (--签收运单轨迹表
    select waybill_no                                                                             --运单号
         , taking_early_scansitecode                                                              --最早揽收网点
         , coalesce(sign_early_scansitecode, outstore_early_scansitecode)   as sign_early_scansitecode --第一次签收网点
--          , if(sign_early_scantime > outstore_early_scantime, sign_early_scansitecode,
--               outstore_early_scansitecode)                             as sign_early_scansitecode --第一次签收网点
         , deliver_early_scansitecode                                                             --第一次派件网点
         , customer_code                                                                          --客户编号CODE
         , customer_name                                                                          --客户编号名称
         , receiver_mobile_phone                                                                  --收件人手机号
         , dispatch_staff_code                                         as wide_staff_no           --宽表员工编码
         , dispatch_staff_name                                         as wide_staff_name         --宽表员工名称
         , nvl(substr(sign_early_scantime, 1, 10),
               substr(outstore_early_scantime, 1, 10))                 as sign_early_scantime     --第一次签收时间
         , case when outstore_early_scantime is null then 1 else 0 end as is_out_sign             --1代表签收,0代表出库
    from jms_dwd.dwd_wide_sign_summary_waybill_dt --签收运单轨迹表
    where dt = '{{ execution_date | cst_ds }}'
),
     order_mark_tmp AS (--订单打标拓展 --确定是按需配送
         select waybill_id  --运单号
              , field_name  --打标字段名
              , field_value --打标字段值
         from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt --订单打标拓展
         where dt between date_add('{{ execution_date | cst_ds }}', -15) and '{{ execution_date | cst_ds }}'
           and field_name = 'ON-DEMAND' --有按需配送的情况
           and field_value = 1          --确定是按需配送
         group by waybill_id --运单号
                , field_name --打标字段名
                , field_value --打标字段值
     ),
     order_delivery_tmp as ( --订单派送记录表 --派送选项
         select waybill_id                         --订单派送记录表
              , delivery_staff_code                --派件业务员编码
              , delivery_option                    --派送选项 1、派送上门  2、放驿站  3、未接
              , upload_time as delivery_input_time --入库时间
         from jms_dwd.dwd_yl_oms_order_delivery_record_base_dt --订单派送记录表
         where dt between date_add('{{ execution_date | cst_ds }}', -7) and '{{ execution_date | cst_ds }}'
         group by waybill_id          --订单派送记录表
                , delivery_staff_code --派件业务员编码
                , delivery_option     --派送选项 1、派送上门  2、放驿站  3、未接
                , upload_time --入库时间
     ),
     call_record_tmp as (--通话记录表
         select sn               -- 话单序列号
              , waybill_no       -- 运单编号
              , staff_no         -- 员工编号
              , staff_name       -- 员工名称
              , start_time       -- 开始时间
              , end_time         -- 结束时间
              , duration_seconds -- 通话时长（秒）
              , connect_status   -- 0未接通 1已接通本方挂断 2已接通对方挂断
              , hangup_reason    -- 未接通挂断原
         from jms_dwd.dwd_sqs_call_record_base_dt --通话记录表
         where dt between date_add('{{ execution_date | cst_ds }}', -7) and '{{ execution_date | cst_ds }}'
         group by sn               -- 话单序列号
                , waybill_no       -- 运单编号
                , staff_no         -- 员工编号
                , staff_name       -- 员工名称
                , start_time       -- 开始时间
                , end_time         -- 结束时间
                , duration_seconds -- 通话时长（秒）
                , connect_status   -- 0未接通 1已接通本方挂断 2已接通对方挂断
                , hangup_reason -- 未接通挂断原
     ),
     network_tmp as (--网点资料
         select code        --站点
              , name
              , agent_code  --代理区
              , agent_name
              , fran_code   --加盟商
              , fran_name
              , provider_id --省
              , provider_desc
              , city_id     --市
              , city_desc
         from jms_dim.dim_network_whole_massage --网点资料
     )
insert
overwrite
table
jms_dm.dm_cusc_demand_sign_taking_call_detail_dt
partition
(
dt
)
select sign.waybill_no                                                             -- 运单号
     , sign.taking_early_scansitecode               as taking_early_scansitecode   -- 最早揽收网点
     , network_taking.name                          as taking_early_scansite_name  -- 最早揽收网点名称
     , network_taking.agent_name                    as taking_agent_name           -- 揽收网点对应的代理区code
     , network_taking.agent_code                    as taking_agent_code           -- 揽收网点对应的代理区名称
     , network_taking.fran_code                     as taking_fran_code            -- 揽收网点对应的加盟商code
     , network_taking.fran_name                     as taking_fran_name            -- 揽收网点对应的加盟商名称
     , network_taking.provider_id                   as taking_provider_id          -- 揽收网点对应的省id
     , network_taking.provider_desc                 as taking_provider_desc        -- 揽收网点对应的省名称
     , network_taking.city_id                       as taking_city_id              -- 揽收网点对应的城市id
     , network_taking.city_desc                     as taking_city_desc            -- 揽收网点对应的城市名称
     , sign.sign_early_scansitecode                 as sign_early_scansitecode     -- 第一次签收网点code
     , network_sign.name                            as sign_early_scansite_name    -- 第一次签收网点名称
     , network_sign.agent_name                      as sign_agent_name             -- 签收网点对应的代理区code
     , network_sign.agent_code                      as sign_agent_code             -- 签收网点对应的代理区名称
     , network_sign.fran_code                       as sign_fran_code              -- 签收网点对应的加盟商code
     , network_sign.fran_name                       as sign_fran_name              -- 签收网点对应的加盟商名称
     , network_sign.provider_id                     as sign_provider_id            -- 签收网点对应的省id
     , network_sign.provider_desc                   as sign_provider_desc          -- 签收网点对应的省名称
     , network_sign.city_id                         as sign_city_id                -- 签收网点对应的城市id
     , network_sign.city_desc                       as sign_city_desc              -- 签收网点对应的城市名称
     , sign.deliver_early_scansitecode              as deliver_early_scansitecode  -- 第一次派件网点code
     , network_deliver.name                         as deliver_early_scansite_name -- 第一次派件网点名称
     , sign.customer_code                                                          -- 客户编号CODE
     , sign.customer_name                                                          -- 客户编号名称
     , sign.sign_early_scantime                                                    -- 第一次签收时间
     , sign.receiver_mobile_phone                                                  -- 收件人手机号
     , sign.wide_staff_no                                                          -- 宽表员工编码
     , sign.wide_staff_name                                                        -- 宽表员工名称
     , order_delivery.delivery_staff_code                                          -- 派件业务员编码
     , order_delivery.delivery_option                                              -- 派送选项 1、派送上门  2、放驿站  3、未接
     , order_delivery.delivery_input_time                                          -- 派送录入时间
     , call_record.staff_no                                                        -- 员工编号
     , call_record.staff_name                                                      -- 员工名称
     , call_record.start_time                       as call_start_time             -- 开始时间
     , call_record.duration_seconds                                                -- 通话时长（秒）
     , call_record.connect_status                                                  -- 0未接通 1已接通本方挂断 2已接通对方挂断
     , 1                                            as is_need_call_num            -- 1代表应电联量(当天签收的运单中是按需配送的 )(和订单打标拓展表join之后,所有的数据都是)
     , if(call_record.waybill_no is not null, 1, 0) as is_have_call_num            -- 1代表已电联(只要拨打就算（只要存在就可以）)电联表都是已电
     , if(call_record.duration_seconds > 6, 1, 0)   as is_gt6_seconds              -- 1代表接通超过6秒(拨打电话且接通超过6秒的电话量（取通话时间最长）)
     , case
           when (call_record.connect_status <> 0 or (call_record.connect_status = 0 and hangup_reason in
                                                                                        (1001, 1002, 2200,
                                                                                         2300, 2400, 2500,
                                                                                         2600, 2701, 2702,
                                                                                         2704, 2705, 2706,
                                                                                         2707, 2708, 2709,
                                                                                         27010, 2711))) and
                ((order_delivery.delivery_option = 1 and sign.is_out_sign = 1) or
                 (order_delivery.delivery_option = 2 and sign.is_out_sign = 0) or
                 (order_delivery.delivery_option = 3 and sign.is_out_sign in (1, 0))) then 1
           else 0 end                               as is_performance              -- 1代表电联履约 履约量是需要根据电联和物流详情进行对比统一才算准确履约
     , '{{ execution_date | cst_ds }}'                                 as dt
from sign_tmp sign
         join order_mark_tmp order_mark
              on order_mark.waybill_id = sign.waybill_no
         left join order_delivery_tmp order_delivery
                   on order_delivery.waybill_id = sign.waybill_no
         left join call_record_tmp call_record
                   on call_record.waybill_no = sign.waybill_no
         left join network_tmp network_taking
                   on network_taking.code = sign.taking_early_scansitecode
         left join network_tmp network_sign
                   on network_sign.code = sign.sign_early_scansitecode
         left join network_tmp network_deliver
                   on network_deliver.code = sign.deliver_early_scansitecode
    where  sign.sign_early_scantime is not null
    
 distribute by dt
;
